# coding:utf-8

import openpyxl

class Excel:

    def __init__(self, fileName, formName=None):
        self.fileName = fileName
        self.formName = formName

    def creatFile(self):
        #  创建excel文件
        workbook = openpyxl.Workbook()   #  创建 一个 excel文件
        if self.formName:
            workbook.create_sheet(self.formName)  # 创建 表单
        workbook.save(self.fileName)   #  保存 excel文件 修改创建之后都需保存

    def gainFormData(self):
        # 获取表单内所有数据
        workbook = openpyxl.load_workbook(self.fileName)    # 打开 excel文件
        sheetnames = workbook.sheetnames    # 获取所有表单名称
        sheet = workbook[self.formName] if self.formName else workbook[sheetnames[0]]  # 选中表单
        data = list(sheet.rows)   # 获取该表单内所有数据
        dataList = [[item.value for item in cases] for cases in data]
        return dataList

    def writeData(self, datalist):
        # 在表单里最新一行写入数据
        workbook = openpyxl.load_workbook(self.fileName)  # 打开 excel文件
        sheetnames = workbook.sheetnames  # 获取所有表单名称
        sheet = workbook[self.formName] if self.formName else workbook[sheetnames[0]]  # 选中表单
        maxrow = sheet.max_row  # 最大行数
        # print('column:',column)
        # maxcolumn = sheet.max_column    # 最大列数
        for i in range(0,len(datalist)):
            sheet.cell(row=maxrow+1, column=i+1, value=datalist[i])
        workbook.save(self.fileName)   #  保存 excel文件 修改创建之后都需保存

    def newWriteData(self, dataListList):
        # 从第一行开始写入数据
        workbook = openpyxl.load_workbook(self.fileName)  # 打开 excel文件
        sheetnames = workbook.sheetnames  # 获取所有表单名称
        sheet = workbook[self.formName] if self.formName else workbook[sheetnames[0]]  # 选中表单
        for i in range(0, len(dataListList)):
            dataList = dataListList[i]
            for ii in range(0, len(dataList)):
                data = dataList[ii]
                sheet.cell(row=i + 1, column=ii + 1, value=data)
        workbook.save(self.fileName)   #  保存 excel文件 修改创建之后都需保存


if __name__ == '__main__':

    pass
